﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;
using System.Data.SqlClient;
using System.Data;

namespace ClassDAL
{
    public class t_class_class_DAL
    {
        /// <summary>
        /// 插入课程信息
        /// </summary>
        /// <param name="classes"></param>
        /// <returns></returns>
        public static int InsertClasses(IList<t_class_class> classes)
        {
            StringBuilder insertBuilder = new StringBuilder();

            foreach (t_class_class cls in classes)
            {
                insertBuilder.AppendLine(string.Format("insert into t_class_classes values('{0}',{1},'{2}',(select ISNULL(MAX(dataTxtid),0) + 1  from t_class_classes where fileId = {3}));", cls.classname, cls.fileid, cls.createtime.ToString("yyyy-MM-dd HH:mm:ss"), cls.fileid));
            }

            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertBuilder.ToString());
        }


        /// <summary>
        /// 根据fileid 获取class信息
        /// </summary>
        /// <param name="fileid">对应的上传的 excel文件 id</param>
        /// <returns></returns>
        public static List<t_class_class> GetClassListWithFileId(int fileid)
        {
            string selectSQL = "select id,classname,fileid,createtime,dataTxtid from t_class_classes where fileid = @fileid order by datatxtid asc;";

            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@fileid",
                Value = fileid
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);

            if (ds == null || ds.Tables.Count == 0) return null;
            DataRowCollection rows = ds.Tables[0].Rows;
            List<t_class_class> classesList = new List<t_class_class>();

            foreach (DataRow row in rows)
            {
                t_class_class model = new t_class_class();
                model.id = Convert.ToInt32(row["id"]);
                model.classname = row["classname"].ToString();
                model.fileid = Convert.ToInt32(row["fileid"]);
                model.createtime = Convert.ToDateTime(row["createtime"]);
                model.dataTxtid = Convert.ToInt32(row["dataTxtid"]);

                classesList.Add(model);
            }


            return classesList;
        }


        /// <summary>
        /// 根据上传的excel文件获取班级数量
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public static int GetClassCountWithFileId(int fileId)
        {
            string getCountSQL = "select COUNT(1) from t_class_classes where fileId = @fileid;";

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), CommandType.Text, getCountSQL, new SqlParameter() { ParameterName = @"fileid", Value = fileId }));
        }


        /// <summary>
        /// 根据班级id获取班级信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static t_class_class GetClassInfoById(int id)
        {
            string selectSQL = "select id,classname,fileid,createtime,datatxtid from t_class_classes where id = @id;";
            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@id",
                Value = id
            };

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);
            if (!reader.Read()) return null;

            var classInfo = new t_class_class();
            classInfo.id = Convert.ToInt32(reader["id"]);
            classInfo.classname = reader["classname"].ToString();
            classInfo.fileid = Convert.ToInt32(reader["fileid"]);
            classInfo.createtime = Convert.ToDateTime(reader["createtime"]);
            classInfo.dataTxtid = Convert.ToInt32(reader["dataTxtid"]);

            reader.Close();


            return classInfo;
        }


        /// <summary>
        /// 获取某个excel表格里最小班级id
        /// </summary>
        /// <param name="fileid"></param>
        /// <returns></returns>
        public static int GetMinClassId(int fileid)
        {
           

            return -1;
        }
    }
}
